﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CinemaBookingProject.DBLayer;

public class DBCinemaRoom : DataBase
{
    private string select = "SELECT ID, AmountOfBookings, MaxRowLength, MaxSeat, Screen FROM CinemaRoom";
    private string delete = "DELETE FROM CinemaRoom";
    private string update = "UPDATE CinemaRoom SET MaxRowLength = @MaxRowLenght, MaxSeat = @MaxSeat, Screen = @Screen";

    /// <summary>
    /// A private method used to add "WHERE" in the statement.
    /// </summary>
    /// <param name="query">Insert the old query string</param>
    /// <param name="parameter">The statement after "WHERE"</param>
    /// <returns>Returns the query string</returns>
    private string WhereAdder(string query, string parameter)
    {
        return query + " WHERE " + parameter;
    }

    /// <summary>
    /// A read method to find a cinema room by id
    /// </summary>
    /// <param id=""></param>
    /// <returns>One cinema room</returns>
    public CinemaRoom Read(int id)
    {
        SqlDataReader rdr = null;
        CinemaRoom cRoom = null;
        try
        {
            SqlCommand cmd;
            Connection.Open();
         
            cmd = new SqlCommand(WhereAdder(select, "ID = @ID"), Connection);
           
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                //, , , , 
                cRoom = new CinemaRoom((int)rdr["ID"], (string)rdr["Screen"], (int)rdr["MaxSeat"], (int)rdr["MaxRowLength"]);
                cRoom.AmountOfBookings = (int)rdr["AmountOfBookings"];
            }
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }
            if (Connection != null)
            {
                Connection.Close();
            }
        }

        return cRoom;
    }

    /// <summary>
    /// A read method to find every cinema rooms
    /// </summary>
    /// <returns></returns>
    public List<CinemaRoom> ReadAll()
    {
        SqlDataReader rdr = null;
        List<CinemaRoom> rooms = new List<CinemaRoom>();

        try
        {
            SqlCommand cmd;
            Connection.Open();

            cmd = new SqlCommand(select, Connection);


            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                //, , , , 
                CinemaRoom cRoom = new CinemaRoom((int)rdr["ID"], (string)rdr["Screen"], (int)rdr["MaxSeat"], (int)rdr["MaxRowLength"]);
                cRoom.AmountOfBookings = (int)rdr["AmountOfBookings"];
                rooms.Add(cRoom);
            }
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }
            if (Connection != null)
            {
                Connection.Close();
            }
        }

        return rooms;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="id"></param>
    /// <param name="maxSeat"></param>
    /// <param name="maxLength"></param>
    /// <param name="Screen"></param>
    /// <returns></returns>
    public int Insert(int id, int maxSeat, int maxLength, string Screen)
    {
        int result = 0;
        try
        {
            string insert = "INSERT INTO CinemaRoom VALUES (@ID, @AmountOfBookings, @MaxRowLength, @MaxSeat, @Screen";
            Connection.Open();
            SqlCommand cmd = new SqlCommand(insert, Connection);
            cmd.Parameters.Add("@ID", SqlDbType.TinyInt).Value = id;
            cmd.Parameters.Add("@AmountOfBookings", SqlDbType.Int).Value = 0;
            cmd.Parameters.Add("@MaxRowLength", SqlDbType.Int).Value = maxLength;
            cmd.Parameters.Add("@MaxSeat", SqlDbType.Int).Value = maxSeat;
            cmd.Parameters.Add("@Screen", SqlDbType.VarChar).Value = Screen;
            result = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            result = -1;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (Connection != null)
            {
                Connection.Close();
            }
        }

        return result;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="room"></param>
    /// <returns></returns>
    public int Update(CinemaRoom room)
    {
        int result = 0;
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand(WhereAdder(update, "ID = @ID"), Connection);
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = room.Id;
            cmd.Parameters.Add("@MaxRowLength", SqlDbType.Int).Value = room.MaxLengthSeats;
            cmd.Parameters.Add("@MaxSeat", SqlDbType.Int).Value = room.MaxSeats;
            cmd.Parameters.Add("@Screen", SqlDbType.VarChar).Value = room.Screen;
            result = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            result = -1;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (Connection != null)
            {
                Connection.Close();
            }
        }

        return result;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public int Delete(int id)
    {
        int result = 0;
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand(WhereAdder(delete, "ID = @ID"), Connection);
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
            result = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            result = -1;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (Connection != null)
            {
                Connection.Close();
            }
        }

        return result;
    }

}   

